﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region 产品

        #region 发布产品
        /// <summary>
        /// 添加新产品
        /// </summary>
        /// <param name="productionsInfo"></param>
        /// <returns></returns>
        public int InsertProducts(ProductInfo productionsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,productionsInfo.UserUid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,productionsInfo.ProId),
                                      DbHelper.MakeInParam("@ProClsId",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(productionsInfo.ProClsId,2)),
                                      DbHelper.MakeInParam("@Numbers",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(productionsInfo.Numbers,100)),
                                      DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NChar,20,productionsInfo.Title),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,productionsInfo.Imgs),
                                      DbHelper.MakeInParam("@OriginalPrice",(DbType)SqlDbType.Money,int.MaxValue,productionsInfo.OriginalPrice),
                                      DbHelper.MakeInParam("@CurrentPrice",(DbType)SqlDbType.Money,int.MaxValue,productionsInfo.CurrentPrice),
                                      DbHelper.MakeInParam("@IsHot",(DbType)SqlDbType.Bit,5,productionsInfo.IsHot),
                                      DbHelper.MakeInParam("@Promotions",(DbType)SqlDbType.Bit,5,productionsInfo.Promotions),
                                      DbHelper.MakeInParam("@ProClsName",(DbType)SqlDbType.NVarChar,60,productionsInfo.ProClsName),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,productionsInfo.Key),
                                      DbHelper.MakeInParam("@Descriptions",(DbType)SqlDbType.NVarChar,200,productionsInfo.Description),
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,productionsInfo.Area),
                                      DbHelper.MakeInParam("@MyClsName",(DbType)SqlDbType.NChar,20,productionsInfo.MyClsName),
                                      DbHelper.MakeInParam("@MyClsId",(DbType)SqlDbType.NVarChar,60,productionsInfo.MyProClsId),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,productionsInfo.Maturity.Length,productionsInfo.Maturity),
                                      DbHelper.MakeInParam("@Explication",(DbType)SqlDbType.NVarChar,int.MaxValue,productionsInfo.Explication),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,productionsInfo.Date.Length,productionsInfo.Date),
                                      DbHelper.MakeOutParam("@OUTNUM",(DbType)SqlDbType.Int,2)
                                  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_insertProductsAndProductsExplication]", parms);
            return Convert.ToInt32((int)parms[parms.Length - 1].Value);
        }
        /// <summary>
        /// 更新新产品
        /// </summary>
        /// <param name="productionsInfo"></param>
        /// <returns></returns>
        public int UpdateProducts(ProductInfo productionsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,productionsInfo.UserUid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,productionsInfo.ProId),
                                      DbHelper.MakeInParam("@ProClsId",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(productionsInfo.ProClsId,2)),
                                      DbHelper.MakeInParam("@Numbers",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(productionsInfo.Numbers,100)),
                                      DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NChar,20,productionsInfo.Title),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,productionsInfo.Imgs),
                                      DbHelper.MakeInParam("@OriginalPrice",(DbType)SqlDbType.Money,int.MaxValue,productionsInfo.OriginalPrice),
                                      DbHelper.MakeInParam("@CurrentPrice",(DbType)SqlDbType.Money,int.MaxValue,productionsInfo.CurrentPrice),
                                      DbHelper.MakeInParam("@IsHot",(DbType)SqlDbType.Bit,5,productionsInfo.IsHot),
                                      DbHelper.MakeInParam("@Promotions",(DbType)SqlDbType.Bit,5,productionsInfo.Promotions),
                                      DbHelper.MakeInParam("@ProClsName",(DbType)SqlDbType.NVarChar,60,productionsInfo.ProClsName),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,productionsInfo.Key),
                                      DbHelper.MakeInParam("@Descriptions",(DbType)SqlDbType.NVarChar,200,productionsInfo.Description),
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,productionsInfo.Area),
                                      DbHelper.MakeInParam("@MyClsName",(DbType)SqlDbType.NChar,20,productionsInfo.MyClsName),
                                      DbHelper.MakeInParam("@MyClsId",(DbType)SqlDbType.NVarChar,60,productionsInfo.MyProClsId),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,productionsInfo.Maturity.Length,productionsInfo.Maturity),
                                      DbHelper.MakeInParam("@Explication",(DbType)SqlDbType.NVarChar,int.MaxValue,productionsInfo.Explication),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,productionsInfo.Date.Length,productionsInfo.Date),
                                      DbHelper.MakeOutParam("@OUTNUM",(DbType)SqlDbType.Int,2)
                                  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_updateProductsAndProductsExplication]", parms);
            return Convert.ToInt32((int)parms[parms.Length - 1].Value);
        }
        /// <summary>
        /// 根据指定的条件更新产品图片
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <param name="imgs"></param>
        /// <returns></returns>
        public int UpdateProductsImgs(string userid, string proid, string imgs)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,proid),
                                      DbHelper.MakeInParam("@imgs",(DbType)SqlDbType.NVarChar,120,imgs)
                                  };
            string commandText = string.Format("UPDATE [dbo].[{0}product] SET [imgs]=@imgs WHERE [userguid]=@userid AND [proid]=@proid",BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 删除产品
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelProducts( string proid)
        {
            int i = 0;
            SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
            conn.Open();
            using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    DbParameter parms1 = DbHelper.MakeInParam("@Proid", (DbType)SqlDbType.NVarChar, 60, proid);
                    string commandText1 = string.Format("DELETE FROM [dbo].[{0}product]  WHERE [proid]=@proid", BaseConfigs.GetTablePrefix);
                    if (DbHelper.ExecuteNonQuery(CommandType.Text, commandText1, parms1) < 0)
                    {
                        conn.Close();
                        return i;
                    }

                    DbParameter parms2 = DbHelper.MakeInParam("@proid", (DbType)SqlDbType.NVarChar, 60, proid);
                    string commandText2 = string.Format("DELETE FROM [dbo].[{0}productsexplication] WHERE [proid]=@proid", BaseConfigs.GetTablePrefix);
                    if (DbHelper.ExecuteNonQuery(trans, CommandType.Text, commandText2, parms2) < 0)
                    {
                        trans.Rollback();
                    }
                    trans.Commit();
                    i=1;

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除产品
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelProducts(string userid, string proid)
        {
            int i = 0;
            SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
            conn.Open();
            using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    DbParameter[] parms1 = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,proid)
                                   };
                    string commandText1 = string.Format("DELETE FROM [dbo].[{0}product]  WHERE [userguid]=@userid AND [proid]=@proid", BaseConfigs.GetTablePrefix);
                    if (DbHelper.ExecuteNonQuery(CommandType.Text, commandText1, parms1) < 0)
                    {
                        conn.Close();
                        return i;
                    }

                    DbParameter parms2 =  DbHelper.MakeInParam("@proid", (DbType)SqlDbType.NVarChar, 60, proid) ;
                    string commandText2 = string.Format("DELETE FROM [dbo].[{0}productsexplication] WHERE [proid]=@proid", BaseConfigs.GetTablePrefix);
                    if (DbHelper.ExecuteNonQuery(trans, CommandType.Text, commandText2, parms2) < 0)
                    {
                        trans.Rollback();
                    }
                    trans.Commit();
                    i = 1;

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();
            return i;
        }
        /// <summary>
        /// 取得基本产品信息不包含产品扩展信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetProducts(string proid)
        {
            DbParameter parms = DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}product] WHERE [proid]=@proid",BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 取得基本产品信息不包含产品扩展信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetProducts(string userid,string proid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,proid),
                                };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}product] WHERE [proid]=@proid AND [userguid]=@userid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据proID取得产品信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetProductsByProId(string proid)
        {
            DbParameter parms = DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid);
            string commandText = string.Format("SELECT A.*,B.explication FROM [dbo].[{0}product] AS A LEFT JOIN [dbo].[{0}productsexplication] AS B ON A.[proid]=B.[proid] WHERE A.[proid]=@proid",BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据用户ID取得所有产品记录信息包含扩展信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetProductsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT A.*,B.explication FROM [dbo].[{0}product] AS A LEFT JOIN [dbo].[{0}productsexplication] AS B ON A.[proid]=B.[proid] WHERE A.[userguid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据用户ID取得所有产品记录信息不包含扩展信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetProductsByUserIdNoExtInfo(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}product]  WHERE [userguid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountProductsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = "SELECT COUNT(proid) FROM [dbo].[zby_product] WHERE [userguid]=@userid";
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        #endregion

        #region 自定义分类

        /// <summary>
        /// 根据用户ID获取自定义分类
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetMyProCls(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid);
            string commandText = "SELECT * FROM [dbo].[zby_myprocls] WHERE [userguid]=@userid ORDER BY [date] desc";
            return DbHelper.ExecuteReader(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据分类ID获取自定义分类
        /// </summary>
        /// <returns></returns>
        public DataTable GetMyProClsByMyClsId(string myclsid)
        {
            DbParameter parms = DbHelper.MakeInParam("@myclsid", (DbType)SqlDbType.NVarChar, 60, myclsid);
            string commandText = "SELECT * FROM [dbo].[zby_myprocls] WHERE [myclsid]=@myclsid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 统计自定义分类总数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountMyProClsRows(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = "SELECT COUNT(myclsid) FROM [dbo].[zby_myprocls] WHERE [userguid]=@userid";
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms),0);
        }
        /// <summary>
        /// 增加自定义分类
        /// </summary>
        /// <param name="myProClsInfo"></param>
        /// <returns></returns>
        public int InsertMyProCls(MyProClsInfo myProClsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,myProClsInfo.UserId),
                                      DbHelper.MakeInParam("@myclsid",(DbType)SqlDbType.NVarChar,60,myProClsInfo.MyClsId),
                                      DbHelper.MakeInParam("@myclsname",(DbType)SqlDbType.NVarChar,20,myProClsInfo.MyClsName),
                                      DbHelper.MakeInParam("@clsimg",(DbType)SqlDbType.NChar,30,myProClsInfo.Img),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,myProClsInfo.Date.Length,myProClsInfo.Date)
                                  };
            string commandText = "INSERT INTO[dbo].[zby_myprocls]([userguid],[myclsid],[myclsname],[clsimg],[date])VALUES(@userid,@myclsid,@myclsname,@clsimg,@date)";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 更新自定义分类
        /// 注：根据USERID AND MYCLSID（两个条件同时满足）更新
        /// </summary>
        /// <param name="myProClsInfo"></param>
        /// <returns></returns>
        public int UpdateMyProCls(MyProClsInfo myProClsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,myProClsInfo.UserId),
                                      DbHelper.MakeInParam("@myclsid",(DbType)SqlDbType.NVarChar,60,myProClsInfo.MyClsId),
                                      DbHelper.MakeInParam("@myclsname",(DbType)SqlDbType.NVarChar,20,myProClsInfo.MyClsName),
                                      DbHelper.MakeInParam("@clsimg",(DbType)SqlDbType.NChar,30,myProClsInfo.Img)
                                  };
            string commandText = "UPDATE [dbo].[zby_myprocls] SET [myclsname]=@myclsname,[clsimg]=@clsimg WHERE[myclsid]=@myclsid AND [userguid]=@userid";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 删除自定义分类
        /// 注：根据USERID AND MYCLSID（两个条件同时满足）更新
        /// </summary>
        /// <param name="myclsid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int DelMyProClsByMyClsIdAndUserId(string myclsid, string userid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@myclsid",(DbType)SqlDbType.NVarChar,60,myclsid)
                                  };
            string commandText = "DELETE FROM[dbo].[zby_myprocls] WHERE [userguid]=@userid AND [myclsid]=@myclsid";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        #endregion
        #endregion
    }
}